﻿using HCQ2_Common.SQL;
using HCQ2_IDAL;
using HCQ2_Model;
using HCQ2_Model.SelectModel;
using HCQ2_Model.Student;
using HCQ2_Model.ViewModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HCQ2_DAL_MSSQL
{
    /// <summary>
    /// 学生相关查询
    /// </summary>
    public partial class T_StudentDAL: IT_StudentDAL
    {
        /// <summary>
        /// 获取学生列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public TableModel GetStudentList(StudentListQuery query)
        {
            StringBuilder where = new StringBuilder(4);
            if (!string.IsNullOrEmpty(query.schoolName))
            {
                where.AppendFormat("s.schoolName='{0}' and ", query.schoolName);
            }
         
            if (query.classId != null)
            {
                where.AppendFormat("s.classId={0} and ", query.classId);
            }
            if (!string.IsNullOrEmpty(query.teacherName))
            {
                where.AppendFormat("s.teacherName like '%{0}%' and ", query.teacherName);
            }
            if (!string.IsNullOrEmpty(query.studentName))
            {
                where.AppendFormat("s.studentName like '%{0}%' and ", query.studentName);
            }

            if (where.Length > 1)
            {
                where.Insert(0, " where ");
                where.Remove(where.Length - 4, 4);
            }

            string sql = string.Format(@"select 
            s.id,s.schoolName,s.classId,c.className,
            s.studentName,s.sex,s.state,s.updateTime,s.updateUser,
            s.createTime,s.createUser,c.teacherName
            from T_Student s
            left join T_Class c on s.classId=c.id 
            {0}", where.ToString());

            var rowsData = SqlHelper.ExecutePageList<StudentList>(query.page, query.rows, "schoolName,classId,studentName", sql);
            int countData = SqlHelper.GetCount(sql);

            return new TableModel()
            {
                rows = rowsData,
                total = countData
            };
        }

        /// <summary>
        /// 获取学校下面的班级
        /// </summary>
        /// <param name="schoolName"></param>
        /// <returns></returns>
        public List<ListBoxModel> ListBySchoolClass(string schoolName)
        {
            string sql = string.Format("select CONVERT(nvarchar,id) value,className text from T_Class where schoolName='{0}'", schoolName);

            List<ListBoxModel> result = SqlHelper.ExecuteList<ListBoxModel>(sql);

            return result;
        }

    }
}
